<?php

/**
 * 数据迁移脚本：从eb_classes表匹配fa_course_type表，插入到fa_subject_lv表
 * 使用纯净的PHP+PDO操作数据库
 * 
 * 逻辑：
 * 1. 读取eb_classes表中包含特定关键词的记录
 * 2. 在fa_course_type表中查找匹配的课程类型
 * 3. 将匹配的数据插入到fa_subject_lv表（避免重复）
 */

// 数据库配置
$config = [
    'host' => 'localhost',
    'dbname' => 'dev002_gxyusheng',  // 请修改为实际数据库名
    'username' => 'root',     // 请修改为实际用户名
    'password' => '159456',     // 请修改为实际密码
    'charset' => 'utf8mb4'
];

class SubjectLvMigrator
{
    private $pdo;

    // 需要匹配的关键词
    private $keywords = ['银龄', '缤纷', '友邻', '艺术', '学校直属'];
    private $keywords2 = ['银龄学院', '缤纷学院', '友邻学院', '艺术学院', '学校直属教学点'];

    // 统计信息
    private $stats = [
        'processed' => 0,
        'matched' => 0,
        'inserted' => 0,
        'updated' => 0,
        'skipped' => 0,
        'errors' => 0,
        'eb_classes_updated' => 0
    ];

    /**
     * 构造函数，初始化数据库连接
     */
    public function __construct($config)
    {
        try {
            $dsn = "mysql:host={$config['host']};dbname={$config['dbname']};charset={$config['charset']}";
            $this->pdo = new PDO($dsn, $config['username'], $config['password']);
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            echo "数据库连接成功\n";
        } catch (PDOException $e) {
            die("数据库连接失败: " . $e->getMessage() . "\n");
        }
    }

    /**
     * 执行迁移
     */
    public function migrate()
    {
        echo "开始数据迁移...\n";
        echo "匹配关键词: " . implode(', ', $this->keywords) . "\n";
        echo str_repeat('-', 50) . "\n";

        try {
            // 获取包含关键词的eb_classes记录
            $classes = $this->getMatchingClasses();
            echo "找到 " . count($classes) . " 条包含关键词的班级记录\n\n";

            foreach ($classes as $class) {
                $this->processClass($class);
            }

            $this->printStats();
            echo "\n数据迁移完成！\n";
        } catch (Exception $e) {
            echo "迁移失败: " . $e->getMessage() . "\n";
            $this->stats['errors']++;
        }
    }

    /**
     * 获取包含关键词的eb_classes记录
     */
    private function getMatchingClasses()
    {
        $whereConditions = [];
        $params = [];

        foreach ($this->keywords as $index => $keyword) {
            $whereConditions[] = "name LIKE :keyword{$index}";
            $params["keyword{$index}"] = "%{$keyword}%";
        }

        $sql = "SELECT id, name, city_id, school_id FROM eb_classes WHERE " . implode(' OR ', $whereConditions);

        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            echo "查询eb_classes表失败: " . $e->getMessage() . "\n";
            return [];
        }
    }

    /**
     * 处理单个班级记录
     */
    private function processClass($class)
    {
        $this->stats['processed']++;

        echo "处理班级: {$class['name']} (ID: {$class['id']})\n";

        // 在fa_course_type表中查找匹配的课程类型
        $courseTypes = $this->findMatchingCourseTypes($class['name']);

        if (empty($courseTypes)) {
            echo "  - 未找到匹配的课程类型\n";
            $this->stats['skipped']++;
            return;
        }

        $subjectLvId = null; // 记录最后操作的fa_subject_lv记录ID

        foreach ($courseTypes as $courseType) {
            $this->stats['matched']++;
            echo "  - 匹配到课程类型: {$courseType['name']} (ID: {$courseType['id']})\n";

            // 根据班级名称中的关键词查找对应的父级ID
            $pid = $this->findParentIdByKeyword($class['name']);
            echo "    - 查找到父级ID: " . ($pid ? $pid : '无') . "\n";

            // 检查是否已存在相同course_type_id的记录
            $existingRecord = $this->findExistingRecord($courseType);

            if ($existingRecord) {
                // 如果存在相同course_type_id的记录，则更新
                if ($this->updateSubjectLv($existingRecord['id'], $courseType, $pid)) {
                    echo "    * 成功更新fa_subject_lv表记录 (ID: {$existingRecord['id']})\n";
                    $this->stats['updated']++;
                    $subjectLvId = $existingRecord['id']; // 记录更新的记录ID
                } else {
                    echo "    * 更新失败\n";
                    $this->stats['errors']++;
                }
            } else {
                // 如果不存在，则插入新记录
                $insertedId = $this->insertSubjectLv($courseType, $pid);
                if ($insertedId) {
                    echo "    * 成功插入到fa_subject_lv表 (ID: {$insertedId})\n";
                    $this->stats['inserted']++;
                    $subjectLvId = $insertedId; // 记录插入的记录ID
                } else {
                    echo "    * 插入失败\n";
                    $this->stats['errors']++;
                }
            }
        }

        // 更新eb_classes表中的subject_lv字段
        if ($subjectLvId) {
            if ($this->updateEbClassesSubjectLv($class['id'], $subjectLvId)) {
                echo "  - 成功更新eb_classes表的subject_lv字段为: {$subjectLvId}\n";
                $this->stats['eb_classes_updated']++;
            } else {
                echo "  - 更新eb_classes表的subject_lv字段失败\n";
                $this->stats['errors']++;
            }
        }

        echo "\n";
    }

    /**
     * 查找匹配的课程类型
     */
    private function findMatchingCourseTypes($className)
    {
        $sql = "SELECT id, name, city_id, school_id FROM fa_course_type";

        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute();
            $courseTypes = $stmt->fetchAll(PDO::FETCH_ASSOC);

            $matches = [];
            foreach ($courseTypes as $courseType) {
                // 检查班级名称是否包含课程类型名称
                if (strpos($className, $courseType['name']) !== false) {
                    $matches[] = $courseType;
                }
            }

            return $matches;
        } catch (PDOException $e) {
            echo "    查询fa_course_type表失败: " . $e->getMessage() . "\n";
            return [];
        }
    }

    /**
     * 查找已存在的记录（基于course_type_id）
     */
    private function findExistingRecord($courseType)
    {
        $sql = "SELECT id, city_id, school_id, name, course_type_id FROM fa_subject_lv WHERE course_type_id = :course_type_id LIMIT 1";

        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([
                'course_type_id' => $courseType['id']
            ]);

            return $stmt->fetch(PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            echo "    查找已存在记录失败: " . $e->getMessage() . "\n";
            return false;
        }
    }

    /**
     * 根据关键词查找对应的父级ID
     */
    private function findParentIdByKeyword($className)
    {
        // 关键词映射关系
        $keywordMapping = [
            '银龄' => '银龄学院',
            '缤纷' => '缤纷学院',
            '友邻' => '友邻学院',
            '艺术' => '艺术学院',
            '学校直属' => '学校直属教学点'
        ];

        foreach ($keywordMapping as $keyword => $parentName) {
            if (strpos($className, $keyword) !== false) {
                // 在fa_subject_lv表中查找对应的父级记录
                $sql = "SELECT id FROM fa_subject_lv WHERE name = :name LIMIT 1";

                try {
                    $stmt = $this->pdo->prepare($sql);
                    $stmt->execute(['name' => $parentName]);
                    $result = $stmt->fetch(PDO::FETCH_ASSOC);

                    if ($result) {
                        return $result['id'];
                    }
                } catch (PDOException $e) {
                    echo "    查找父级ID失败: " . $e->getMessage() . "\n";
                }
            }
        }

        return null; // 未找到对应的父级
    }

    /**
     * 插入到fa_subject_lv表
     */
    private function insertSubjectLv($courseType, $pid = null)
    {
        $sql = "INSERT INTO fa_subject_lv (city_id, school_id, name, course_type_id, pid, createtime, updatetime, status) 
                VALUES (:city_id, :school_id, :name, :course_type_id, :pid, :createtime, :updatetime, :status)";

        try {
            $stmt = $this->pdo->prepare($sql);
            $result = $stmt->execute([
                'city_id' => $courseType['city_id'],
                'school_id' => $courseType['school_id'],
                'name' => $courseType['name'],
                'course_type_id' => $courseType['id'],
                'pid' => $pid,
                'createtime' => time(),
                'updatetime' => time(),
                'status' => 1
            ]);

            if ($result) {
                // 返回插入记录的ID
                return $this->pdo->lastInsertId();
            }
            return false;
        } catch (PDOException $e) {
            echo "    * 插入错误: " . $e->getMessage() . "\n";
            return false;
        }
    }

    /**
     * 更新fa_subject_lv表记录
     */
    private function updateSubjectLv($recordId, $courseType, $pid = null)
    {
        $sql = "UPDATE fa_subject_lv SET city_id = :city_id, school_id = :school_id, name = :name, pid = :pid, updatetime = :updatetime, status = :status 
                WHERE id = :id";

        try {
            $stmt = $this->pdo->prepare($sql);
            $result = $stmt->execute([
                'city_id' => $courseType['city_id'],
                'school_id' => $courseType['school_id'],
                'name' => $courseType['name'],
                'pid' => $pid,
                'updatetime' => time(),
                'status' => 1,
                'id' => $recordId
            ]);

            return $result;
        } catch (PDOException $e) {
            echo "    * 更新错误: " . $e->getMessage() . "\n";
            return false;
        }
    }

    /**
     * 更新eb_classes表中的subject_lv字段
     */
    private function updateEbClassesSubjectLv($classId, $subjectLvId)
    {
        $sql = "UPDATE eb_classes SET subject_lv = :subject_lv WHERE id = :id";

        try {
            $stmt = $this->pdo->prepare($sql);
            $result = $stmt->execute([
                'subject_lv' => $subjectLvId,
                'id' => $classId
            ]);

            return $result;
        } catch (PDOException $e) {
            echo "    * 更新eb_classes表错误: " . $e->getMessage() . "\n";
            return false;
        }
    }

    /**
     * 打印统计信息
     */
    private function printStats()
    {
        echo str_repeat('=', 50) . "\n";
        echo "迁移统计:\n";
        echo "- 处理的班级记录: {$this->stats['processed']}\n";
        echo "- 匹配的课程类型: {$this->stats['matched']}\n";
        echo "- 成功插入记录: {$this->stats['inserted']}\n";
        echo "- 成功更新记录: {$this->stats['updated']}\n";
        echo "- 更新eb_classes记录: {$this->stats['eb_classes_updated']}\n";
        echo "- 跳过的记录: {$this->stats['skipped']}\n";
        echo "- 错误数量: {$this->stats['errors']}\n";
        echo str_repeat('=', 50) . "\n";
    }
}

// 执行迁移
try {
    $migrator = new SubjectLvMigrator($config);
    $migrator->migrate();
} catch (Exception $e) {
    echo "脚本执行失败: " . $e->getMessage() . "\n";
}
